package database;

//imports
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JOptionPane;
import domein.Klant;

public class Mapper
	{
		  //JDBC driver name and database URL
		  private final String JDBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
		  private final String DATABASE_URL = "jdbc:odbc:MS Access-database;DBQ=BINFSeaVisie";
		 
		  // declare Connection and Statement for accessing and querying database
		  private Connection connection;
		  private Statement statement;
		  private static Mapper deMapper;
		  private static Klant klant;
		  
		  public static Mapper getInstance()
		  {	
				if (deMapper == null)
					deMapper = new Mapper();
				return deMapper;
		  }
		  // constructor connects to database
		  private Mapper()
		  {
		     // connect to database books 
		     try
		     {
		        // load database driver class
				Class.forName( JDBC_DRIVER );
					
		        // establish connection to database
		        connection = DriverManager.getConnection( DATABASE_URL );

		        // create Statement for querying database
		        statement = connection.createStatement();
		     }
		     catch ( SQLException sqlException )
		     {
		    	 JOptionPane.showMessageDialog( null, sqlException.getMessage(),
		    	           "Database Error", JOptionPane.ERROR_MESSAGE );

		    	        System.exit( 1 );
		     }
		     // detect problems loading database driver
		     catch ( ClassNotFoundException classNotFound )
		     {
		        JOptionPane.showMessageDialog( null, classNotFound.getMessage(),
		           "Driver Not Found", JOptionPane.ERROR_MESSAGE );

		        System.exit( 1 );
		     }
		  }
		  
		  public Statement getStatement()
			  {
				  return statement;
			  }
		  
		  public void closeConnection()
		   {
		        try
		        {
		           statement.close();
		           connection.close();
		        }

		        // handle exceptions closing statement and connection
		        catch ( SQLException sqlException )
		        {
		         JOptionPane.showMessageDialog( null,
		            sqlException.getMessage(), "Database Error",
		            JOptionPane.ERROR_MESSAGE );

		         System.exit( 1 );
		      	 }
		     }//end closeConnection
		  
		  public static Klant gegevensKlantOphalen(String id)
		  {
			  Mapper deMapper= getInstance();
			  Statement statement= deMapper.getStatement() ;
			      try
			      
			      {
			    	// query database
			    	  String Qry = String.format("Select * FROM tblKlanten Where Id='%s'",id);
			        ResultSet resultSet =
			           statement.executeQuery(Qry);
			        if(resultSet != null)
			        	{
			        		resultSet.next();
			        	     
					        klant = new Klant(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5),resultSet.getString(6),resultSet.getString(7),resultSet.getInt(8),resultSet.getString(9),resultSet.getString(10),resultSet.getString(11),resultSet.getString(12),resultSet.getString(13),resultSet.getString(14),resultSet.getString(15),resultSet.getString(16),resultSet.getString(17),resultSet.getString(18),resultSet.getString(19),resultSet.getString(20));
					        						       
			        	}
			        else
			        	{
			        		System.out.print("geen");
			        	}
				       }  // end try

				     // detect problems interacting with the database
				     catch ( SQLException sqlException )
				     {
				    	 
				        JOptionPane.showMessageDialog( null, sqlException.getMessage(),
				         "Database Error", JOptionPane.ERROR_MESSAGE );

				        //System.exit( 1 );
				     }
				     
				    return klant; 
		  }//end gegevensKlantOphalen
		  
		}  // end class Databank
